/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Fees;
import Category.CategoryBean;
import Course.CourseBean;
import FeeType.FeeTypeBean;
import SubFeeHead.SubFeeHeadBean;
import java.sql.*;
import java.util.Iterator;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {
    static Connection con=null;
    
     public static int add(Vector<Object> fees) throws SQLException
    {
        int i=0,k[]=null;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("insert into fees values(feesid.nextval,?,?,?,?,?,?,?,sysdate,?)");
            Iterator it=fees.iterator();
            while(it.hasNext())
            {
            FeesBean fb=(FeesBean)it.next();
            delete(fb.getFeesUnique());
            pre.setInt(1, fb.getFeeTypeId());
            pre.setFloat(2, fb.getAmount());
            pre.setInt(3, fb.getCourseId());
            pre.setString(4, fb.getSteam());
            pre.setInt(5, fb.getCoursePeriod());
            pre.setInt(6, fb.getCategoryId());
            pre.setString(7, fb.getFeesUnique());
            pre.setString(8, fb.getSession());
            pre.addBatch();
            }
            k=pre.executeBatch();
            if(k.length!=0)
            {
                i=1;
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
        finally
        {
            con.close();
        }
        return i;
    }
    public static int getMaxId() throws SQLException
    {
        int i=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("select max(fees_id) from fees");
            ResultSet rs=pre.executeQuery();
            while(rs.next())
            {
                i=rs.getInt(1);
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
        return i;
    }
public static FeesBean viewSpecific(String cname, String val) throws SQLException
{
    FeesBean fb= new FeesBean();
    FeeTypeBean ftb=new FeeTypeBean();
    CourseBean cb=new CourseBean();
    CategoryBean ctb=new CategoryBean();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from fees where "+cname+"=?");
        pre.setString(1, val);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            fb.setFeesId(rs.getInt(1));
            fb.setFeeTypeId(rs.getInt(2));
            fb.setAmount(rs.getFloat(3));
            fb.setCourseId(rs.getInt(4));
            fb.setSteam(rs.getString(5));
            fb.setCoursePeriod(rs.getInt(6));
            fb.setCategoryId(rs.getInt(7));
            fb.setFeesUnique(rs.getString(8));
            fb.setEntryDate(rs.getDate(9));
            fb.setSession(rs.getString(10));
            ftb.setFeeTypeId(rs.getInt(11));
            ftb.setFeeTypeName(rs.getString(12));
            //ftb.setFeeDuration(rs.getInt(13));
            ftb.setEntryDate(rs.getDate(14));
            fb.setFeeTypeBean(ftb);
            cb.setCourseId(rs.getInt(15));
            cb.setCourseName(rs.getString(16));
            cb.setCourseType(rs.getInt(17));
            cb.setCoursePeriod(rs.getInt(18));
            cb.setStream(rs.getString(19));
            cb.setEntryDate(rs.getDate(20));
            fb.setCourseBean(cb);
            ctb.setCategoryId(rs.getInt(21));
            ctb.setCategory(rs.getString(22));
            ctb.setEntryDate(rs.getDate(23));
            fb.setCategoryBean(ctb);
            
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return fb;
}
    
public static Vector viewAll() throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from fees f1 left join sub_fee_type s1 on f1.sub_fee_type_id=s1.sub_fee_type_id left join fee_type f2 on s1.fee_type_id=f2.fee_type_id left join course c1 on f1.course_id=c1.course_id left join category c2 on f1.category_id=c2.category_id");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
                FeesBean fb= new FeesBean();
                FeeTypeBean ftb=new FeeTypeBean();
                SubFeeHeadBean sbh=new SubFeeHeadBean();
                CourseBean cb=new CourseBean();
                CategoryBean ctb=new CategoryBean();
            fb.setFeesId(rs.getInt(1));
            fb.setFeeTypeId(rs.getInt(2));
            fb.setAmount(rs.getFloat(3));
            fb.setCourseId(rs.getInt(4));
            fb.setSteam(rs.getString(5));
            fb.setCoursePeriod(rs.getInt(6));
            fb.setCategoryId(rs.getInt(7));
            fb.setFeesUnique(rs.getString(8));
            fb.setEntryDate(rs.getDate(9));
            fb.setSession(rs.getString(10));
            sbh.setSubFeeType(rs.getString(13));
            fb.setSubFeeHeadBean(sbh);
            ftb.setFeeTypeName(rs.getString(17));
            fb.setFeeTypeBean(ftb);
            cb.setCourseName(rs.getString(20));
            fb.setCourseBean(cb);
            ctb.setCategory(rs.getString(26));
            fb.setCategoryBean(ctb);
            list.add(fb);
            
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return list;
    }
    public static int delete(String uniq) throws SQLException
        {
            int i=0;
            try
            {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("delete from fees where FEES_UNIQUE=?");
              pre.setString(1,uniq);
              i=pre.executeUpdate();
            }
            catch(Exception e)
            {
               return i; 
            }
            finally
            {
              con.close();  
            }
            return i;
        }    
     public static int getAmount(String uniq) throws SQLException
        {
            int i=0;
            try
            {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("select amount from fees where fees_unique=?");
              pre.setString(1,uniq);
              ResultSet rs=pre.executeQuery();
              while(rs.next())
              {
                  i=rs.getInt(1);
              }
            }
            catch(Exception e)
            {
               return i; 
            }
            finally
            {
              con.close();  
            }
            return i;
        }
     
     
     public static Vector viewSpecificSearch(int feetype, int courseid ) throws SQLException
{
    Vector<Object> list=new Vector<Object>();
    
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from fees f1 left join sub_fee_type s1 on f1.sub_fee_type_id=s1.sub_fee_type_id left join fee_type f2 on s1.fee_type_id=f2.fee_type_id left join course c1 on f1.course_id=c1.course_id left join category c2 on f1.category_id=c2.category_id where f2.FEE_TYPE_ID=? and c1.course_id=?");
        pre.setInt(1, feetype);
        pre.setInt(2, courseid);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
                FeesBean fb= new FeesBean();
                FeeTypeBean ftb=new FeeTypeBean();
                SubFeeHeadBean sbh=new SubFeeHeadBean();
                CourseBean cb=new CourseBean();
                CategoryBean ctb=new CategoryBean();
            fb.setFeesId(rs.getInt(1));
            fb.setFeeTypeId(rs.getInt(2));
            fb.setAmount(rs.getFloat(3));
            fb.setCourseId(rs.getInt(4));
            fb.setSteam(rs.getString(5));
            fb.setCoursePeriod(rs.getInt(6));
            fb.setCategoryId(rs.getInt(7));
            fb.setFeesUnique(rs.getString(8));
            fb.setEntryDate(rs.getDate(9));
            fb.setSession(rs.getString(10));
            sbh.setSubFeeType(rs.getString(13));
            fb.setSubFeeHeadBean(sbh);
            ftb.setFeeTypeName(rs.getString(17));
            fb.setFeeTypeBean(ftb);
            cb.setCourseName(rs.getString(20));
            fb.setCourseBean(cb);
            ctb.setCategory(rs.getString(26));
            fb.setCategoryBean(ctb);
            list.add(fb);
            
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{
        con.close();
    }
    return list;
    }
}
